create database  Student02
on
(
	name='Student02',
	filename='D:\Student02.mdf',
	size=20,
	maxsize=300,
	filegrowth=50
)
log on
(
	name='Student02_log',
	filename='D:\Student02_log.ldf',
	size=20,
	maxsize=300,
	filegrowth=50
)
go
use   Student02
go
create table StudenIfo
(
	stuNo int identity(2501,1) primary key,
	stuName nvarchar(20) not null unique, 
	stuAge int   null ,
	studdRess nvarchar(20) not null,
	stuSeat int  ,
	stuSext int check(stuSext in('1','0')) default('0')
)
insert into StudenIfo(stuName,stuAge,studdRess,stuSeat,stuSext ) values('张秋利','20','美国硅谷','1','1'),('李斯文','18','湖北武汉','2','0'),('马文才','22','湖南长沙','3','1'), 
('欧阳俊雄','21','湖北武汉','4','0'),('梅超凤','20','湖北武汉','5','1'),('陈炫风','20','美国硅谷','6','1'),('陈风','20','美国硅谷','7','0') 

create table Class01
(
	examNo int identity(1,1) primary key,
	stuNo char(20) null,
	writtenExam int  ,
	labExam int 
)
insert into Class01(stuNo,writtenExam,labExam) values ('2501','50','70'),('2502','60','65'),('2503','86','85'),('2503','40','80'),('2504','70','90'),('2505','85','90')

select * from StudenIfo
select * from Class01
--数据如图片1,使用上次作业的数据

--1.查询学生的姓名，年龄，笔试成绩和机试成绩

select stuName 姓名, stuAge 年龄, writtenExam 笔试成绩,labExam 机试成绩 from StudenIfo si 
inner join Class01 cl on si.stuNo = cl.stuNo

--2.查询笔试和机试成绩都在60分以上的学生的学号，姓名，笔试成绩和机试成绩

select si.stuNo,stuName ,writtenExam ,labExam  from StudenIfo si 
	inner join Class01 cl on si.stuNo = cl.stuNo 
		where writtenExam > 60 and labExam > 60

--3.查询所有学生的学号，姓名，笔试成绩，机试成绩，没有参加考试的学生的成绩以NULL值填充

select si.stuNo ,stuName ,writtenExam ,labExam from StudenIfo si 
	LEFT join Class01 cl on si.stuNo = cl.stuNo

--4.查询年龄在20以上（包括20）的学生的姓名，年龄，笔试成绩和机试成绩，并按笔试成绩降序排列

select stuName,stuAge,writtenExam,labExam from StudenIfo si 
	inner join Class01 cl on si.stuNo = cl.stuNo 
		where stuAge >= 20 order by writtenExam DESC

--5.查询男女生的机试平均分

select stuSext ,AVG(labExam) from StudenIfo si 
	inner join Class01 cl on si.stuNo = cl.stuNo 
		group by stuSext 

--6.查询男女生的笔试总分

select stuSext,SUM(writtenExam) from StudenIfo si 
	inner join Class01 cl on si.stuNo = cl.stuNo 
		group by stuSext